#!/usr/bin/env python
#   File: LibrarySql.py
# Author: shadytyrant@gmail.com
#   Date: 2009-11-25
#  Notes: a module that handles SQL querys for the library database
#         supports adding, removing, editing, and searching information.
#--------------------------------------------

import sqlite3

class Library:
    def __init__(self, database):
        # Class variables
        self.GENRA = ('fiction', 'non-fiction', 'science fiction', 'technical', 'reference')

        # Open database
        self.connect = sqlite3.connect(database)
        self.cursor = self.connect.cursor()


    def Perform(self, sqlStatment):
        self.cursor.execute(sqlStatment)
        self.connect.commit()


    def CheckId(self, ID):
        sqlStatment = 'SELECT * FROM library WHERE id = (%d)' % int(ID)
        self.Perform(sqlStatment)

        info = ''
        for row in self.cursor:
            info += str(row)

        if not info:
            errorString = 'No book with id of %d' % int(ID)
            raise LibraryError(errorString)


    def CloseConnection(self):
        self.cursor.close()
        self.connect.commit()

    #--------------------------------------------

    def SearchAll(self):
        sqlStatment = 'SELECT * FROM library ORDER BY id'
        self.Perform(sqlStatment)

        allData = []
        for row in self.cursor:
            allData.append(row)

        if not allData:
            raise LibraryError("No books found in library")
        else:
            return allData


    def SearchById(self, ID):
        try:
            sqlStatment = 'SELECT * FROM library WHERE id = (%d)' % int(ID)
            self.Perform(sqlStatment)
        except(ValueError):
            raise LibraryError("Invalid Search")

        allData = []
        for row in self.cursor:
            allData.append(row)

        if not allData:
            errorString = 'No books with id of %d' % int(ID)
            raise LibraryError(errorString)
        else:
            return allData


    def SearchByTitle(self, title):
        try:
            sqlStatment = 'SELECT * FROM library WHERE title = ("%s")' % title
            self.Perform(sqlStatment)
        except(ValueError):
            raise LibraryError("Invalid Search")

        allData = []
        for row in self.cursor:
            allData.append(row)

        if not allData:
            errorString = 'No books with title of %s' % title
            raise LibraryError(errorString)
        else:
            return allData


    def SearchByAuthor(self, author):
        try:
            sqlStatment = 'SELECT * FROM library WHERE author = ("%s")' % author
            self.Perform(sqlStatment)
        except(ValueError):
            raise LibraryError("Invalid Search")

        allData = []
        for row in self.cursor:
            allData.append(row)

        if not allData:
            errorString = 'No books with author of %s' % author
            raise LibraryError(errorString)
        else:
            return allData


    def SearchByGenra(self, genra):
        try:
            sqlStatment = 'SELECT * FROM library WHERE genra = ("%s")' % genra
            self.Perform(sqlStatment)
        except(ValueError):
            raise LibraryError("Invalid Search")

        allData = []
        for row in self.cursor:
            allData.append(row)

        if not allData:
            errorString = 'No books under genra of %s' % genra
            raise LibraryError(errorString)
        else:
            return allData


    def SearchByPublisher(self, publisher):
        try:
            sqlStatment = 'SELECT * FROM library WHERE publisher = ("%s")' % publisher
            self.Perform(sqlStatment)
        except(ValueError):
            raise LibraryError("Invalid Search")

        allData = []

        for row in self.cursor:
            allData.append(row)

        if not allData:
            errorString = 'No books with publisher %s' % publisher
            raise LibraryError(errorString)
        else:
            return allData

    #--------------------------------------------

    def AddBook(self, title, author, genra, publisher, seriesIndex=1):
        try:
            sqlStatment = 'INSERT INTO library VALUES("%s", "%d", "%s", NULL, "%s", "%s")' % (genra, seriesIndex, author, publisher, title)
            self.Perform(sqlStatment)
        except:
            raise LibraryError('Could not add book to library')


    def RemoveBook(self, ID):
        self.CheckId(int(ID))
        try:
            sqlStatment = 'DELETE FROM library WHERE id = (%d)' % int(ID)
            self.Perform(sqlStatment)
        except:
            raise LibraryError('Could not remove book from library')


    def EditSeriesIndex(self, ID, newSeriesIndex):
        self.CheckId(ID)
        try:
            sqlStatment = 'UPDATE library SET seriesIndex = (%d) WHERE id = (%d)' % (newSeriesIndex, ID)
            self.Perform(sqlStatment)
        except:
            raise LibraryError('Could not edit series index')


    def EditTitle(self, ID, newTitle):
        self.CheckId(ID)
        try:
            sqlStatment = 'UPDATE library SET title = ("%s") WHERE id = (%d)' % (newTitle, ID)
            self.Perform(sqlStatment)
        except:
            raise LibraryError('Could not edit title')


    def EditAuthor(self, ID, newAuthor):
        self.CheckId(ID)
        try:
            sqlStatment = 'UPDATE library SET author = ("%s") WHERE id = (%d)' % (newAuthor, ID)
            self.Perform(sqlStatment)
        except:
            raise LibraryError('Could not edit author')


    def EditGenra(self, ID, newGenra):
        self.CheckId(ID)
        try:
            sqlStatment = 'UPDATE library SET genra = ("%s") WHERE id = (%d)' % (newGenra, ID)
            self.Perform(sqlStatment)
        except:
            raise LibraryError('Could not edit genra')


    def EditPublisher(self, ID, newPublisher):
        self.CheckId(ID)
        try:
            sqlStatment = 'UPDATE library SET publisher = ("%s") WHERE id = (%d)' % (newPublisher, ID)
            self.Perform(sqlStatment)
        except:
            raise LibraryError('Could not edit publisher')

#--------------------------------------------

class LibraryError(Exception):
   def __init__(self, value):
       self.parameter = value

   def __str__(self):
       return repr(self.parameter)